import mysql.connector
import xlwings as xw
import pickle
import traceback
from datetime import datetime

data_source_type = 'mysql'
# data_source_type = 'file'


def main():
    start_time_str, end_time_str = '2023-01-31 20', '2023-03-01 09'
    total_record_list = []
    detail_record_list = []
    total_file_name = 'total.txt'
    detail_file_name = 'detail.txt'
    if data_source_type == 'mysql':
        detail_sql, total_sql = get_sql()
        mydb = mysql.connector.connect(
             host='172.16.10.220',  # 数据库主机地址
             port=3306,  # 数据库端口号
            #host='127.0.0.1',  # 数据库主机地址
            #port=23306,  # 数据库端口号
            user='root',  # 数据库用户名
            passwd='Pr0dkcd_',  # 数据库密码
            database='kcdoc' # 数据库名
        )
        print(mydb)
        mycursor = mydb.cursor(dictionary=True)
        print(mycursor)
        params = (start_time_str, end_time_str, )
        record_list = query_data(mycursor, total_sql, params)
        write_data_file(total_file_name, record_list)
        total_record_list = record_list

        record_list = query_data(mycursor, detail_sql, params)
        write_data_file(detail_file_name, record_list)
        detail_record_list = record_list

    else:
        total_record_list = read_data_file(total_file_name)
        detail_record_list = read_data_file(detail_file_name)

    try:
        print('Excel创建开始')
        app = xw.App(visible=True,add_book=False)
        wb = app.books.add()
        wb.sheets.add('统计')
        # wb.sheets.add('明细')
        # wb.sheets['sheet1'].delete()
        wb.sheets['sheet1'].name = '明细'
        total_sheet = wb.sheets['统计']

        total_head_list = []
        total_head_list.append('序号')
        total_head_list.append('车间')
        total_head_list.append('所属组织')
        total_head_list.append('姓名')
        total_head_list.append('工号')
        total_head_list.append('班次日期')
        total_head_list.append('班次')
        total_head_list.append('类型')
        total_head_list.append('应巡检次数')
        total_head_list.append('实际巡检次数')
        total_head_list.append('漏巡检次数')
        write_excel_template(total_sheet, total_head_list, total_record_list)

        detail_sheet = wb.sheets['明细']
        detail_head_list = []
        detail_head_list.append('序号')
        detail_head_list.append('车间')
        detail_head_list.append('所属组织')
        detail_head_list.append('姓名')
        detail_head_list.append('工号')
        detail_head_list.append('日期')
        detail_head_list.append('班次')
        detail_head_list.append('类型')
        detail_head_list.append('小时')
        detail_head_list.append('巡检时间')
        write_excel_template(detail_sheet, detail_head_list, detail_record_list)

        wb.save('{}_{}巡检情况统计.xlsx'.format(change_time_str(start_time_str), change_time_str(end_time_str)))
    except BaseException as ex:
        print('生成巡检情况统计.xlsx出错')
        print(traceback.format_exc())
    finally:
        wb.close()
        app.quit()
        print('Excel创建结束')


def get_sql():
    detail_sql = '''
                    SELECT
                    get_leaderships ( su.organization_id, '/' ) AS '所属组织',
                    su.user_realname AS '姓名',
                    t.user_no AS '工号',
                    t.day AS '日期',
                    IFNULL(t.NAME,'未排班') AS '班次',
                    CASE t.type WHEN 0 THEN '正常班' WHEN 1 THEN '加班' ELSE '未排班' END AS '类型',
                    t.hour AS '小时',
                    t.report_time AS '巡检时间'
                    FROM
                    (
                            SELECT
                            ins.user_no,
                            ins.day,
                            shift.NAME,
                            shift.type,
                            ins.HOUR,
                            ins.report_time
                            FROM
                            kc_inspection ins
                            LEFT JOIN
                            (
                                    SELECT
                                    us_d.shift_id,
                                    us_d.user_no,
                                    us_d.detail_day_id,
                                    det.real_start,
                                    det.real_end,
                                    def.NAME,
                                    det.type
                                    FROM
                                    (
                                            SELECT
                                            shift_id,
                                            user_no,
                                            detail_day_id
                                            FROM
                                            kc_shift_user_day
                                            UNION ALL
                                            SELECT
                                            shift_id,
                                            user_no,
                                            detail_day_id
                                            FROM
                                            hi_kc_shift_user_day
                                    ) us_d
                                    INNER JOIN kc_shift_definition def ON us_d.shift_id = def.id
                                    INNER JOIN kc_shift_detail_day det ON us_d.detail_day_id = det.id
                            ) shift
                            ON ins.user_no = shift.user_no
                            AND DATE_ADD(ins.day, interval ins.hour hour) >= shift.real_start
                            AND DATE_ADD(ins.day, interval ins.hour hour) < shift.real_end
                            WHERE DATE_ADD(ins.DAY, interval ins.HOUR hour) >= STR_TO_DATE(%s,'%Y-%m-%d %H')
                            AND DATE_ADD(ins.DAY, interval ins.HOUR hour) <= STR_TO_DATE(%s,'%Y-%m-%d %H')
                            AND ins.user_no!='001'
                    ) t
                    LEFT JOIN sys_user su ON t.user_no = su.user_no
                    ORDER BY
                    get_leaderships ( su.organization_id, '/' ),
                    t.user_no,
                    t.day,
                    t.HOUR
                    '''
    total_sql = '''
                    SELECT
                    get_leaderships ( su.organization_id, '/' ) AS '所属组织',
                    su.user_realname AS '姓名',
                    t.user_no AS '工号',
                    t.day AS '班次日期',
                    IFNULL(t.NAME,'未排班') AS '班次',
                    CASE t.type WHEN 0 THEN '正常班' WHEN 1 THEN '加班' ELSE '未排班' END AS '类型',
                    t.应巡检次数,
                    t.实际巡检次数,
                    (t.应巡检次数-t.实际巡检次数) as '漏巡检次数'
                    FROM
                    (
                            SELECT
                            ins.user_no,
                            ifnull(shift.start_day,ins.day) as day,
                            MIN(shift.name) AS name,
                            shift.type,
                            COUNT(1) AS '应巡检次数',
                            COUNT(ins.report_time) AS '实际巡检次数'
                            FROM
                            kc_inspection ins
                            LEFT JOIN
                            (
                                    SELECT
                                    us_d.shift_id,
                                    us_d.user_no,
                                    us_d.detail_day_id,
                                    det.start_day,
                                    det.real_start,
                                    det.real_end,
                                    def.NAME,
                                    det.type
                                    FROM
                                    (
                                            SELECT
                                            shift_id,
                                            user_no,
                                            detail_day_id
                                            FROM
                                            kc_shift_user_day
                                            UNION ALL
                                            SELECT
                                            shift_id,
                                            user_no,
                                            detail_day_id
                                            FROM
                                            hi_kc_shift_user_day
                                    ) us_d
                                    INNER JOIN kc_shift_definition def ON us_d.shift_id = def.id
                                    INNER JOIN kc_shift_detail_day det ON us_d.detail_day_id = det.id
                            ) shift
                            ON ins.user_no = shift.user_no
                            AND DATE_ADD(ins.day, interval ins.hour hour) >= shift.real_start
                            AND DATE_ADD(ins.day, interval ins.hour hour) < shift.real_end
                            WHERE DATE_ADD(ins.DAY, interval ins.HOUR hour) >= STR_TO_DATE(%s,'%Y-%m-%d %H')
                            AND DATE_ADD(ins.DAY, interval ins.HOUR hour) <= STR_TO_DATE(%s,'%Y-%m-%d %H')
                            AND ins.user_no!='001'
                            GROUP BY
                            ins.user_no,
                            ifnull(det.start_day,ins.day),
                            shift.type
                    ) t
                    LEFT JOIN sys_user su ON t.user_no = su.user_no
                    ORDER BY
                    get_leaderships ( su.organization_id, '/' ),
                    t.user_no,
                    t.day,
                    t.type
            '''
    return detail_sql, total_sql


def format2str(data):
    data_type = type(data)
    if data_type is bytearray:
        result = data.decode('UTF-8')
    elif data_type is str:
        result = data.encode().decode('UTF-8')
    else:
        result = data

    if result is None:
        result = ''
    return result


def write_excel_template(sh, head_list, record_list):
    all_list = []
    # 写入head
    print('{}sheet写入开始'.format(sh.name))
    # sh.range('A1:K1').value = head_list
    all_list.append(head_list)
    row_idx = 0
    col_idx = 0
    for record in record_list:
        col_list = []
        col_idx = 0
        for head in head_list:
            org = format2str(record['所属组织'])
            if head == '序号':
                col_list.append(row_idx+1)
            elif head == '车间':
                col_list.append(org[0:org.find('/')])
            else:
                col_list.append(format2str(record[head]))
            col_idx += 1
        all_list.append(col_list)
        row_idx += 1
    print('row_idx:{} col_idx:{}'.format(row_idx, col_idx))
    sh.range((1, 5), (row_idx, 5)).api.NumberFormat = "@"
    sh.range('A1').value = all_list
    sh.range((1,1), (row_idx, col_idx)).columns.autofit()
    sh.range((1,1), (1, col_idx)).color = 217, 217, 217  # 设置单元格的填充颜色
    set_line_style(sh.range((1,1), (row_idx+1, col_idx)).api)
    print('{}sheet写入完毕'.format(sh.name))


def query_data(mycursor, sql, params):
    mycursor.execute(sql, params)
    return mycursor.fetchall()


def write_data_file(file_name, record_list):
    print('{}文件写入开始'.format(file_name))
    with open(file_name, 'wb') as f:  # 打开文件
        # for record in record_list:
        #     # # 保存查询结果到文件中
        #     pickle.dump(record, f)
        pickle.dump(record_list, f)
    print('{}文件写入结束'.format(file_name))


def read_data_file(file_name):
    record_list = []
    # try:
    print('{}文件读取开始'.format(file_name))
    with open(file_name, 'rb') as f:
        # while True:
        #     record = pickle.load(f)
        #     if record:
        #         record_list.append(record)
        #     else:
        #         break
        record_list.extend(pickle.load(f))
    print('{}文件读取完毕'.format(file_name))
    # except EOFError as eof:
    #     print('{}文件读取完毕'.format(file_name))
    return record_list


def change_time_str(start_time_str):
    return datetime.strftime(datetime.strptime(start_time_str, '%Y-%m-%d %H'), '%Y年%m月%d日%H时')


def set_line_style(api):
    api.Borders(7).LineStyle = 1  # 划左边边框
    api.Borders(7).Weight = 2
    api.Borders(8).LineStyle = 1  # 划顶部边框
    api.Borders(8).Weight = 2
    api.Borders(9).LineStyle = 1  # 划底部边框
    api.Borders(9).Weight = 2
    api.Borders(10).LineStyle = 1  # 划右部边框
    api.Borders(10).Weight = 2
    api.Borders(11).LineStyle = 1  # 划内部竖线
    api.Borders(11).Weight = 2
    api.Borders(12).LineStyle = 1  # 划内部横线
    api.Borders(12).Weight = 2


if __name__ == '__main__':
    main()